// 第一题
select team from (
   WITH temp AS
   (SELECT team,(YEAR - row_number () over (PARTITION BY team ORDER BY YEAR)) AS r FROM t1)  // 根据球队和年份排名，再用年份减去排名号，若连续 则求差结果一样
    SELECT team FROM temp GROUP BY team,r HAVING COUNT(1) >= 3) t  //根据球队和上一步中求出的差值分组，若大于等于三 则表示连续三年夺冠
group by team  //如果有同一支球队多次三连冠，则去重



//第二题
WITH temp as (
    SELECT id, time,price,
    lag(price) over(partition by id order by time) as last,    //按id分组 找出每条记录的上个时间的价格
    lead(price) OVER(PARTITION BY id ORDER BY  time) as next   //按id分组 找出每条记录的下个时间的价格
    from t2 
)
select id,time,if(price > last and price > next,'波峰','波谷') as desc from temp  //用if判断是波峰还是波谷
where (price > last and price > next) or (price < last and price < next)   //当当前记录的价格小于上一条的价格并且小于下一条的价格为波谷
                                                                           //当当前记录的价格大于上一条的价格并且大于下一条的价格为波峰
																		   
																		
//第三题
  //第一小题
	select id,
	(unix_timestamp(concat(replace(max(dt),'/','-'),':00')) - unix_timestamp(concat(replace(min(dt),'/','-'),':00')))/60 as duration, //计算时间差
	count(1) as stepLength  //计算次数
	  from t3 group by id  // 根据id分组
  //第二小题
  select id,
(unix_timestamp(concat(replace(max(dt),'/','-'),':00')) - unix_timestamp(concat(replace(min(dt),'/','-'),':00')))/60 as duration,
count(1) as stepLength 
from
    (with tmp as (  //计算每条数据与上一条数据的时间差 若大于30分钟，则flag字段用1表示，否则用0表示
    select id,
        dt, 
        if((unix_timestamp(concat(replace(dt,'/','-'),':00')) - unix_timestamp(concat(replace(lag(dt) over(partition by id order by dt),'/','-'),':00')))/60 > 30,1,0) as flag
    from t3
    )
    select id,dt,sum(flag) over(partition by id order by dt rows between unbounded preceding and current row) as flag from tmp) tmp2 // 将flag字段从组内第一行加到当前行，一个阶段内的flag一致
group by id,flag  //根据id和时间区间标识分组
  
  
